package com.example.spring.mapper;

import com.example.spring.Dto.AddressCount;
import com.example.spring.Dto.Company;
import com.example.spring.Dto.CompanyCountDTO;
import com.example.spring.Dto.CompanyTypeCount;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CompanyMapper {
    @Select({
            "<script>",
            "SELECT company_name, company_person, company_range",
            "FROM companies",
            "WHERE company_name IS NOT NULL",
            "AND company_person IS NOT NULL",
            "AND company_range IS NOT NULL",
            "AND company_person NOT IN ('未提供联系人', 'null', '')",
            "LIMIT #{limit}",
            "</script>"
    })
    List<Company> selectValidCompanies(int limit);

    @Select({
            "<script>",
            "SELECT address, COUNT(*) AS count",
            "FROM companies",
            "WHERE address IS NOT NULL", // 排除空值
            "GROUP BY address",
            "ORDER BY count DESC", // 按数量降序排列
            "</script>"
    })
    List<AddressCount> getAddressCounts();


    @Select({
            "<script>",
            "SELECT ",
            "    TRIM(REPLACE(REPLACE(ct.company_type_name, '湖南', ''), '|厂家', '')) AS company_type_name,",
            "    COUNT(*) AS count",
            "FROM ",
            "    companies c",
            "JOIN ",
            "    company_types ct ON c.company_type_id = ct.company_type_id",
            "GROUP BY ",
            "    TRIM(REPLACE(REPLACE(ct.company_type_name, '湖南', ''), '|厂家', ''))",
            "ORDER BY ",
            "    count DESC",
            "LIMIT 10",
            "</script>"
    })
    List<CompanyTypeCount> getTopCompanyTypeCounts();

    @Select("SELECT COUNT(*) AS count FROM companies")
    @Results({
            @Result(property = "count", column = "count")
    })
    CompanyCountDTO getCompanyCount();

}
